package com.bw.mapper;

import com.bw.bean.OrderInfo;
import com.bw.bean.Vo;
import com.bw.bean.Vo1;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
* @author 25199
* @description 针对表【order_info(订单表 订单表)】的数据库操作Mapper
* @createDate 2025-03-15 14:20:33
* @Entity com.bw.bean.OrderInfo
*/
@Mapper
public interface OrderInfoMapper {

    @Select("select sum(final_total_amount)  from order_info")
    Double t1();

    @Select("select bt.name,sum(o.final_total_amount) value from order_info o\n" +
            "left outer join order_detail od on o.id = od.order_id\n" +
            "left join sku_info si on si.id = od.sku_id\n" +
            "left join base_trademark bt on bt.id = si.tm_id\n" +
            "group by bt.name order by value")
    List<Vo> t2();

    @Select("select bc1.name,sum(o.final_total_amount) value from order_info o\n" +
            "left outer join order_detail od on o.id = od.order_id\n" +
            "left join sku_info si on si.id = od.sku_id\n" +
            "left join base_category3 b on b.id = si.category3_id\n" +
            "left join base_category2 c on c.id = b.category2_id\n" +
            "left join base_category1 bc1 on bc1.id = c.category1_id\n" +
            "group by bc1.name;")
    List<Vo> t3();

    @Select("select '用户' name,a1,a2 from\n" +
            "(select count(*) a1 from order_info where timestampdiff(year ,create_time,now())<1) a,\n" +
            "(select count(*) a2 from order_info where timestampdiff(year ,create_time,now())>=1) ab")
    List<Vo1> t4();

    @Select("select c.name,sum(o.final_total_amount) value from order_info o\n" +
            "left outer join order_detail od on o.id = od.order_id\n" +
            "left join sku_info si on si.id = od.sku_id\n" +
            "left join base_category3 b on b.id = si.category3_id\n" +
            "left join base_category2 c on c.id = b.category2_id\n" +
            "group by c.name order by value;")
    List<Vo> t5();

    @Select("select concat(substr(ui.birthday,3,1) ,'0后') name,sum(o.final_total_amount)value from order_info o\n" +
            "left join user_info ui on ui.id = o.user_id\n" +
            "group by name;")
    List<Vo> t6();

    @Select("select (yys)name,sum(final_total_amount) value from order_info where yys is not null\n" +
            "group by yys;\n")
    List<Vo> t7();

    @Select("select bt.name,if(ui.gender='F','女','男') a1,sum(final_total_amount) a2 from order_info o\n" +
            "left outer join order_detail od on o.id = od.order_id\n" +
            "left join sku_info si on si.id = od.sku_id\n" +
            "left join base_trademark bt on bt.id = si.tm_id\n" +
            "left join user_info ui on ui.id = o.user_id\n" +
            "group by bt.name,ui.gender")
    List<Vo1> t8();
}




